--sp_help Customer_Open_Order_LineItem
ALTER proc sp_change_lineItem_proc
(
	@lineItem_id 		int, 
	@salesOrder_id 		int, 
	@qty 			int, 
	@requested_date 	datetime, 
	@unit_price 		float,
	@amount 		float, 
	@user_id 		varchar(10), 
	@user_name 		varchar(50), 
	@reason 		varchar(50), 
	@is_taxable 		int, 
	@is_cancel 		int, 
	@lineitem_desc 		varchar(500), 
	@update_status 		int,
	@line_item_loop 	int,
	@po_lineitem_no 	int,
	@increment_status 	int output, 
	@lineItem_update_status int, 
	@is_expedite 		int,
	@customer_partnumber VARCHAR(50),
	@PRODUCT_ID		INT,
	@IS_NEW_CUSTOMER_PART_NUMBER INT, --0 -TRUE 1 -FALSE
	@COOL_CUSTOM_PRICE_FIELD1 float, 
	@COOL_CUSTOM_TEXT_FIELD1 varchar(1000)
)
AS
	SET NOCOUNT ON
 	/* Check if there is any change in the lineItem like price, quanity, requeste date, unitPrice  */
	declare @db_qty int
	declare @db_price float
	declare @db_date datetime
	declare @db_taxable int
	declare @db_cancel int
	declare @db_lineitem_desc varchar(500)
 	declare @db_customer_partnumber varchar(50)
	declare @compare_desc varchar(10)
	declare @db_po_lineitem_no int 
	declare @db_is_expedite int
	declare @compare_req_date varchar(10)
	declare @compare_partnumber varchar(10)
	DECLARE @DB_PRODUCT_ID INT
	DECLARE @DB_COOL_LINE_ITEM_NO INT
	DECLARE @DB_COOL_CUSTOM_PRICE_FIELD1 float
	DECLARE @DB_COOL_CUSTOM_TEXT_FIELD1 varchar(1000)
	DECLARE @IS_SAMPLE INT
 	
	DECLARE @CUSTOMER_ID INT
	
	SELECT @CUSTOMER_ID = CUSTOMER_ID, @IS_SAMPLE = SO_IS_SAMPLE FROM SALES_ORDER WHERE SALESORDER_ID = @salesOrder_id
	
	IF ( @IS_NEW_CUSTOMER_PART_NUMBER IS NOT NULL AND @IS_NEW_CUSTOMER_PART_NUMBER = 0 AND LEN(LTRIM(RTRIM(@customer_partnumber))) > 0 )
	 BEGIN
		IF ( (SELECT COUNT(*) CNT FROM CUSTOMER_PARTNUMBERS WHERE CUSTOMER_ID = @CUSTOMER_ID AND PRODUCT_ID = @product_id AND CUSTOMER_PARTNUMBER = LTRIM(RTRIM(@customer_partnumber)) AND ODM_ID IS NULL) = 0 )
		 BEGIN
			INSERT INTO CUSTOMER_PARTNUMBERS( CUSTOMER_ID, PRODUCT_ID, CUSTOMER_PARTNUMBER, LABEL_NUMBER ) VALUES( @CUSTOMER_ID, @product_id, LTRIM(RTRIM(@customer_partnumber)), '' )
	 	 END
	 END

 	SELECT @db_po_lineitem_no = (SELECT isNull(COOL_PO_Line_Item_No,0) FROM Customer_Open_Order_LineItem WHERE LineItem_ID = @lineItem_id)

	SELECT @DB_COOL_LINE_ITEM_NO = COOL_LINE_ITEM_NO, @db_qty = COOL_Qty, @db_price = COOL_Unit_Cost, @db_date = COOL_Requested_Date, @db_taxable = COOL_Taxable, @db_cancel = COOL_Is_Cancel, @db_lineitem_desc =  COOL_Description , @db_is_expedite = COOL_Is_Expedite, @db_customer_partnumber = COOL_CUSTOMER_PARTNUMBER, @DB_PRODUCT_ID = PRODUCT_ID, 
			@DB_COOL_CUSTOM_PRICE_FIELD1 = COOL_CUSTOM_PRICE_FIELD1, @DB_COOL_CUSTOM_TEXT_FIELD1 = COOL_CUSTOM_TEXT_FIELD1 
	FROM Customer_Open_Order_LineItem WHERE LineItem_ID = @lineItem_id

	if( ( @lineitem_desc <> @db_lineitem_desc ) or ( @db_lineitem_desc IS NULL ) )
  	begin
    		set @compare_desc ='TRUE'
  	end
  	else
  	begin
    		set @compare_desc ='FALSE'
  	end

    if( ( @customer_partnumber <> @db_customer_partnumber ) or ( @db_customer_partnumber IS NULL) )
    begin
        set @compare_partnumber ='TRUE'
    end
    else
    begin
        set @compare_partnumber ='FALSE'
    end
 /*
  * Insert values into Customer_Change_Order_LineItem from Customer_Open_Order_LineItem
  */
	if ( ( @db_date <> @requested_date ) or ( @db_date IS NULL ) )
 	begin
 		set @compare_req_date = 'TRUE'	
 	end
 	else
 	begin
 		set @compare_req_date = 'FALSE'
 	end

 	/* Insert into CCOL for old Sales Orders */
 	/*
 	 * Vasanth 09/09/2003
 	 * this complete code to update the table for legacy orders is useless now since in our database we have updated the tables 
 	 * with the relavent info since it was required for the booking report. so i have to change the if statement.
 	 */
 	declare @old_change_number int
 	select @old_change_number = ChangeNo from Customer_Change_Order_LineItem where SalesOrder_ID = @salesorder_id and LineItem_ID = @lineItem_id
 	if( @old_change_number is null )
 	begin
	 	INSERT INTO Customer_Change_Order_LineItem (LineItem_ID, SalesOrder_ID,ChangeNo, CCO_Line_Item_No,CCO_PO_Line_Item_No,
 		Product_ID, CCO_Qty, CCO_Unit_Cost, CCO_Amount, CCO_Requested_Date, 
 		CCO_Booking_Date,CCO_Sugg_Schedule_Date_With_Substitutables,
 		CCO_Sugg_Schedule_Date_Without_Substitutables,CCO_Schedule_Date,
         	CCO_Revised_Date,CCO_Taxable, Timestamp)
 	 	(SELECT LineItem_ID, SalesOrder_ID, 0 ,COOL_Line_Item_No,COOL_PO_Line_Item_No,
 		Product_ID, COOL_Qty, COOL_Unit_Cost, COOL_Amount, COOL_Requested_Date, 
 		COOL_Booking_Date,COOL_Sugg_Schedule_Date_With_Substitutables,
 		COOL_Sugg_Schedule_Date_Without_Substitutables,COOL_Schedule_Date,
 		COOL_Revised_Date,COOL_Taxable, COOL_Booking_Date FROM Customer_Open_Order_LineItem cool 
 		WHERE LineItem_ID = @lineItem_id)	
 	end

 	/* Insert into CCOL for old Sales Orders */
 	declare @sales_change_number int 
 	declare @lineitem_change_number int
 	/* Insert into CCOL if any changes */
 	select @sales_change_number =  isNull(max(ChangeNo),0) from customer_change_order_lineitem where LineItem_ID = @lineItem_id 

 	if( ( @db_qty <> @qty ) or ( @db_price <> @unit_price ) OR  ( @DB_PRODUCT_ID <> @PRODUCT_ID ) )
	begin
 		set @sales_change_number = @sales_change_number + 1
 		
		/*
	 	 * Modified by Vasanth Tatta, 09/09/2003
	 	 * The changed value should be the current one not the old
	 	 * This logic was required for the booking report
	 	 * previously if a legacy order was changed then a 0 is entered with the original value of the COOL
	 	 * and 1 is entered with again the COOL table original values. This is changed here. The changed values should be reflected.
	 	 */
	 	INSERT INTO Customer_Change_Order_LineItem (LineItem_ID, SalesOrder_ID,ChangeNo, CCO_Line_Item_No, CCO_PO_Line_Item_No,
	 	Product_ID, CCO_Qty, CCO_Unit_Cost, CCO_Amount, CCO_Requested_Date, CCO_Booking_Date,CCO_Sugg_Schedule_Date_With_Substitutables,
	 	CCO_Sugg_Schedule_Date_Without_Substitutables,CCO_Schedule_Date, CCO_Revised_Date,CCO_Taxable,CCO_Status)
 	
	 	 (SELECT LineItem_ID, SalesOrder_ID, @sales_change_number ,COOL_Line_Item_No, @po_lineitem_no,
	 		@PRODUCT_ID, @qty, @unit_price, @amount, @requested_date, 
	 		COOL_Booking_Date,COOL_Sugg_Schedule_Date_With_Substitutables,
	 		COOL_Sugg_Schedule_Date_Without_Substitutables,COOL_Schedule_Date,
	 		COOL_Revised_Date,@is_taxable, COOL_Status FROM Customer_Open_Order_LineItem 
	 		WHERE LineItem_ID = @lineItem_id)

	 	/* Select the idenetity(previous id of row) */
 		declare @changeItem_id int
 		SELECT @changeItem_Id = (SELECT @@IDENTITY)

	 	/* Select the max changenumber */
 		declare @change_number int
 		SELECT @change_number = (SELECT MAX(CCO_Change_Number) FROM Customer_Change_Order_LineItem WHERE LineItem_Id = @lineItem_id)

 		if (@change_number is null)
 			set @change_number = 1
 		else
 			set @change_number = @change_number + 1

	 	/* Update the Customer_Cancel_Order_LineItem for CancelById , CancelByName, Change_Number */
 		UPDATE Customer_Change_Order_LineItem SET CCO_Change_Number = @change_number, CCO_Change_By_ID = @user_id,
	 	CCO_Change_By_Name = @user_name, TimeStamp = CURRENT_TIMESTAMP 	WHERE ChangeItem_ID = @changeItem_id

 		/* Update the LineItem from the Customer_Open_Order_LineItem with quantity,price,amount, date and COOL_description. */
 		UPDATE Customer_Open_Order_LineItem SET COOL_Qty = @qty, COOL_Unit_Cost = @unit_price, COOL_amount = @amount, COOL_Requested_Date = @requested_date, COOL_Last_Updated_By_ID = @user_id,COOL_Last_Updated_By_Name = @user_name, TimeStamp = CURRENT_TIMESTAMP, PRODUCT_ID = @PRODUCT_ID 
 		WHERE LineItem_Id = @lineItem_id
 	
 		set @increment_status 		= 1
 		set @lineItem_update_status 	= 1
 	end


 	if( ( ( @db_po_lineitem_no <> @po_lineitem_no) or ( @compare_req_date = 'TRUE' ) or ( @db_taxable <> @is_taxable ) or ( @db_cancel <> @is_cancel ) or ( @compare_desc = 'TRUE' ) OR  (@compare_partnumber = 'TRUE' ) ) )
 	begin
 		UPDATE Customer_Open_Order_LineItem SET COOL_Requested_Date = @requested_date, COOL_Last_Updated_By_ID = @user_id,
 		COOL_Last_Updated_By_Name = @user_name, TimeStamp = CURRENT_TIMESTAMP, COOL_Taxable = @is_taxable, COOL_Is_Cancel = @is_cancel, COOL_Description = @lineitem_desc ,COOL_PO_Line_Item_No = @po_lineitem_no, COOL_CUSTOMER_PARTNUMBER = @customer_partnumber
 		WHERE LineItem_Id = @lineItem_id
 	
 		UPDATE Customer_Change_Order_LineItem SET CCO_Requested_Date = @requested_date, CCO_Change_By_ID = @user_id, CCO_Change_By_Name = @user_name, /*TimeStamp = CURRENT_TIMESTAMP,*/ CCO_Taxable = @is_taxable, CCO_PO_Line_Item_No = @po_lineitem_no, CCO_CUSTOMER_PARTNUMBER = @customer_partnumber
		WHERE LineItem_Id = @lineItem_id and ChangeNo = @sales_change_number
 	end
 
 
	if ( (@is_expedite <> @db_is_expedite) OR (ISNULL(@COOL_CUSTOM_PRICE_FIELD1,0) <> ISNULL(@DB_COOL_CUSTOM_PRICE_FIELD1,0)) OR (ISNULL(@COOL_CUSTOM_TEXT_FIELD1,'') <> ISNULL(@DB_COOL_CUSTOM_TEXT_FIELD1,'')) )
	begin
		UPDATE Customer_Open_Order_LineItem SET COOL_Is_Expedite = @is_expedite, COOL_Last_Updated_By_ID = @user_id,
		COOL_Last_Updated_By_Name = @user_name, TimeStamp = CURRENT_TIMESTAMP, COOL_CUSTOM_PRICE_FIELD1 = @COOL_CUSTOM_PRICE_FIELD1, COOL_CUSTOM_TEXT_FIELD1 = @COOL_CUSTOM_TEXT_FIELD1  WHERE LineItem_Id = @lineItem_id	
	end

 	if( @line_item_loop = 1 and @lineItem_update_status = 1 )
 	begin
 		select @sales_change_number =  max(ChangeNo) from Change_Sales_Order where SalesOrder_ID = @salesorder_id
 		select @lineitem_change_number =   max(ChangeNo) from Customer_Change_Order_LineItem where SalesOrder_ID = @salesorder_id

 		set @sales_change_number = @sales_change_number + 1

 		INSERT INTO Change_Sales_Order(SalesOrder_ID,ChangeNo,Customer_ID,CSO_Customer_PO_Number,CSO_Contact_Person,
 		CSO_Sales_Person,CSO_Shipping_Method,CSO_Shipping_Account,CSO_Billing_Address_ID,CSO_Shipping_Address_ID,
 		CSO_Comments,CSO_Shipping_Instructions,CSO_EndUser,CSO_Status,CSO_Booking_Date,CSO_Is_Sample,CSO_Is_Cancel,
 		CSO_Is_Pending,CSO_Is_Shipped,CSO_Entered_By_ID,CSO_Entered_By_Name,CSO_Sales_Tax_Type,CSO_Sub_Total,
 		CSO_Sales_Tax,CSO_Frieght_Tax,CSO_Misc,Alarm_ID,Timestamp,Regional_Rep,Shipping_Terms,Delivery_Terms,
 		CSO_Misc_Name,CSO_Show_Pro_Forma_Invoice,CSO_Reason, CSO_Sales_Rep)
 	
 		(SELECT SalesOrder_ID,@sales_change_number,Customer_ID,SO_Customer_PO_Number,SO_Contact_Person,
 		SO_Sales_Person,SO_Shipping_Method,SO_Shipping_Account,SO_Billing_Address_ID,SO_Shipping_Address_ID,
 		SO_Comments,SO_Shipping_Instructions,SO_EndUser,SO_Status,SO_Booking_Date,SO_Is_Sample,SO_Is_Cancel,
 		SO_Is_Pending,SO_Is_Shipped,SO_Entered_By_ID,SO_Entered_By_Name,SO_Sales_Tax_Type,SO_Sub_Total,
 		SO_Sales_Tax,SO_Frieght_Tax,SO_Misc,Alarm_ID,Timestamp,Regional_Rep,Shipping_Terms,Delivery_Terms,
 		SO_Misc_Name,SO_Show_Pro_Forma_Invoice,'', SO_Sales_Rep
 		FROM Sales_Order WHERE SalesOrder_id = @salesorder_id)
 	end
 	
	/* Insert the holds accordingly */
 	if( ( @db_qty <> @qty ) or ( @db_price <> @unit_price) or ( @db_date <> @requested_date ) or ( @db_cancel <> @is_cancel ) OR ( @DB_PRODUCT_ID <> @PRODUCT_ID ) )
	begin

 		UPDATE Sales_Order SET SO_Is_Pending=0 WHERE SalesOrder_ID = @salesOrder_id		
 		UPDATE Sales_Hold_Table SET Hold_Reason_Code = '0', Is_Approved = '1', Hold_By_User = 'SYSTEM', Timestamp = CURRENT_TIMESTAMP WHERE LineItem_ID = @lineItem_id 
 		and HOLD_By_Group IN ( select HOLD_By_Group FROM Customer_hold_Table where Customer_ID = @CUSTOMER_ID and IS_Sample = @IS_SAMPLE and IS_Approved = 1 )
 		/* To set Cool_Status to Hold  after changing the LineItem*/ 
 		UPDATE Customer_Open_Order_LineItem SET COOL_Status = 'HOLD' WHERE LineItem_ID = @lineItem_id         		 
 	end

	/* RELEASE RESERVED LOTS FOR THE OLD PART NUMBER IF THE PART NUMBER OR QTY IS CHANGED */
	IF ( ( @DB_QTY <> @QTY ) OR ( @DB_PRODUCT_ID <> @PRODUCT_ID ) )
	 BEGIN
		DECLARE @PROCESSSTEP_ID INT
		SELECT @PROCESSSTEP_ID = PROCESSSTEP_ID FROM PRODUCT WHERE PRODUCT_ID = @DB_PRODUCT_ID
		EXEC sp_release_reserved_lots @DB_COOL_LINE_ITEM_NO, @salesOrder_id, @PROCESSSTEP_ID
		UPDATE CUSTOMER_OPEN_ORDER_LINEITEM SET COOL_SUGG_SCHEDULE_DATE_WITHOUT_SUBSTITUTABLES = NULL, COOL_REVISED_DATE = NULL, COOL_Schedule_Date = NULL WHERE SALESORDER_ID = @salesOrder_id AND COOL_LINE_ITEM_NO = @DB_COOL_LINE_ITEM_NO
	 END
return
